<?  if ( ! defined('BASEPATH')) exit('No direct script access allowed');
 
class ServiceUsuario{
     
    function returnUsuariosEspecialSelect(){
        $CI =& get_instance();
        $em=$CI->doctrine->em;
         
        $qb = $em->createQueryBuilder();
        $qb->select("u.iduser as value, u.usuario as option")
        ->from('Entity\User','u')
        ->from('Entity\Perfil','p')
        ->where('p.idperfil<>5')
        ->andwhere('p.idperfil=u.idperfil')
        ->andwhere('u.eliminado=0')
        ->andwhere('p.eliminado=0');
         
        $query = $qb->getQuery();
        $array = $query->getArrayResult();
        return $array;
        }
     
    function lista(){
        $CI =& get_instance();
        $em=$CI->doctrine->em;
         
        $qb = $em->createQueryBuilder();
        $qb->select("u.nombres as nombres, u.apellidos as apellidos, u.dni as dni,u.fechanacimiento as fechanacimiento, u.telefono as telefono,  u.email as email, u.celular as celular,  r.idrol as idrol, r.rol as rol, u.idusuario as id")
        ->from('Entity\Usuario','u')
        ->from('Entity\Rol','r')
        ->where('r.idrol=u.idrol')
        ->andwhere('u.eliminado=0')
        ->andwhere('r.eliminado=0');
         
        $query = $qb->getQuery();
        $array = $query->getArrayResult();
        return $array;
    }
     
       
     
    function data($id){
        $CI =& get_instance();
        $em=$CI->doctrine->em; 
                 
        $query = $em->createQuery(
        'SELECT p.nombres as nombres,
        p.apellidos as apellidos,
        p.telefono as telefono,
        p.email as email,
        p.dni as dni,
        pf.idperfil as perfil,
        p.idpersona as idpersona,
        d.iddistrito as distrito,
        u.lugar as lugar,
        u.codigousuario as codigousuario,
        u.usuario as usuario,
        u.estado as estado,
        n.idnacionalidad as nacionalidad,
        u.iduser as id  
        FROM Entity\User u, Entity\Perfil pf, Entity\Persona p, Entity\Distrito d, Entity\Nacionalidad n
        WHERE u.iduser= :id and d.iddistrito=p.iddistrito and pf.idperfil=u.idperfil and p.idpersona=u.idpersona and n.idnacionalidad=p.idnacionalidad and pf.eliminado=0 and p.eliminado=0 and u.eliminado=0 and d.eliminado=0 and n.eliminado=0')
        ->setParameter('id', $id);
 
        $array = $query->getArrayResult();
        return $array;
    }
     
    function login($usuario,$password){
        $CI =& get_instance();
        $em=$CI->doctrine->em;
                 
        $query = $em->createQuery(
        "SELECT u.idperfil as perfil,
        u.iduser as iduser,
        p.nombres as nombre,
        p.apellidos as apellido
        FROM Entity\User u, Entity\Persona p 
        WHERE u.usuario= :email and u.password= :password and p.idpersona=u.idpersona and u.estado=1 and u.eliminado=0 and p.eliminado=0")
        ->setParameter('email', $usuario)
        ->setParameter('password', md5($password));
 
        $array = $query->getArrayResult();
        return $array;
        }
     
    function verificaPass($id,$password){
        $CI =& get_instance();
        $em=$CI->doctrine->em;
                 
        $query = $em->createQuery(
        "SELECT u.tipo as perfil,
        u.iduser as iduser,
        p.nombres as nombre,
        p.apellidos as apellido
        FROM Entity\User u, Entity\Persona p 
        WHERE u.iduser= :id and u.password= :password and p.idpersona=u.idpersona and u.eliminado=0 and p.eliminado=0")
        ->setParameter('id', $id)
        ->setParameter('password', md5($password));
 
        $array = $query->getArrayResult();
        if(count($array)>0)
        return true; else return false;
        }
     
     
}
?>